package com.example.springboot2.Service;

import com.example.springboot2.CommonHelper.RedisHelper;
import com.example.springboot2.DB_Helper.JDBCUtils;
import com.example.springboot2.Model.CauseModel;
import com.example.springboot2.Model.Sys_User;
import com.example.springboot2.Model.TestModel;
import org.springframework.data.redis.serializer.RedisSerializer;
import org.springframework.data.redis.serializer.StringRedisSerializer;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

@Component
public class Sys_UserService {
    public List<Sys_User> GetEntityByUserCode(String Sys_UserCode)
    {
        Connection conn = JDBCUtils.findConnection();      //JDBCUtils 自己定义的一个类
        PreparedStatement pre = null;
        ResultSet res = null;
        String sql = "select * from Sys_User where Sys_UserCode='"+Sys_UserCode+"' ";
        try {
            pre = conn.prepareStatement(sql);
            res = pre.executeQuery();
            //调用将结果集转换成实体对象方法
            List<Sys_User> list = JDBCUtils.Populate(res,Sys_User.class);  //.populate(res, TestModel.class);
            //循环遍历结果
            for(int i=0;i<list.size();i++){
                Sys_User model = (Sys_User) list.get(i);
            }
            return  list;

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
        return null;
    }
    public List<Sys_User> GetPageList(int limit,int page)
    {
        String sql=" select * from Sys_User where 1=1  order by Sys_UserCode ";
        Integer StartNum=(page-1)*limit;
        sql=sql+" OFFSET "+Integer.valueOf(StartNum).intValue()+" Row FETCH NEXT "+Integer.valueOf(limit).intValue()+" rows only ";
        Connection conn = JDBCUtils.findConnection();      //JDBCUtils 自己定义的一个类
        PreparedStatement pre = null;
        ResultSet res = null;
        try {
            pre = conn.prepareStatement(sql);
            res = pre.executeQuery();
            //调用将结果集转换成实体对象方法
            List<Sys_User> list = JDBCUtils.Populate(res,Sys_User.class);  //.populate(res, TestModel.class);
            //循环遍历结果
            for(int i=0;i<list.size();i++){
                Sys_User model = (Sys_User) list.get(i);
            }
            return  list;

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
        return null;

    }
    public Integer GetCount()
    {
        String sql=" select count(1) from Sys_User where 1=1  ";
        Connection conn = JDBCUtils.findConnection();      //JDBCUtils 自己定义的一个类
        PreparedStatement pre = null;
        ResultSet res = null;
        try {
            pre = conn.prepareStatement(sql);
            res = pre.executeQuery();
            while (res.next()) {
                System.out.println("【SUCC】获取 SQL-SERVER 的连接已成功。[1]="+res.getString(1));
                return  Integer.parseInt(res.getString(1));
            }

            return  0;

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
}
